1. Introduction
Sitecore V5 offers you a convenient and flexible solution of creating a custom data provider. The Data Provider allows a User to work with Items located in an external database in the same way as if they were located in the Master database. This article contains a ready-made example of an SQL custom data provider with the source code.
2. Northwind Data Provider Architecture
A data provider is a custom class which supplies data to Sitecore from external sources.
In this example, the Sitecore.Data.DataProviders.NorthwindDataProvider class dynamically builds Items based on the data retrieved from Northwind sample MSSQL database and provides them as children of /content/Home/ Northwind Data Provider in the Master database. Keep in mind that you work with an external database despite the fact that the Items are shown as if they were in the Master database.
2.1. Northwind Template
In Sitecore 5, each Item must be based on a certain template. In our example, the Northwind Customer template with the fields listed below is used:
- ContactName : Text
- CompanyName : Text
- Address : Text
- City : Text
<param desc="fieldsNames">...</param> parameter of the Dataprovider definition in web.config file defines the list of fields which can be modified via the Northwind data provider. You can save changes of the fields defined in this parameter (e.g. layout, renderings and sublayouts won’t be saved).
2.2. Data Providers
Please refer to the ‘Integrating External Data Sources’ article for detailed information on the integration of external data sources.
A custom data provider should implement methods of the Sitecore.Data.DataProviders base class.
This particular example implements the following methods:
- GetItemDefinition
- GetItemFields
- GetChildIDs
- GetParentID
- SaveItem
All these methods are placed in the Sitecore.Data.DataProviders.NorthwindDataProvider example class.
2.3. Passing Parameters to the Constructor
We can pass all necessary parameters to the constructor of the data provider class using the web.config parameters:
<northwindDataProvider type="Sitecore.Data.DataProviders.NorthwindDataProvider, Sitecore.NorthwindDataProvider">
<param desc="connection">server=(local);User=sa;password=12345;database=northwind</param>
<param desc="table">Customers</param>
<param desc="fieldsNames">ContactName,CompanyName,Address,City</param>
<param desc="idField">CustomerID</param>
<param desc="nameField">ContactName</param>
<param desc="filter">Country='germany'</param>
<param desc="templateID">{31975406-5987-4390-9151-8F28D11683A4}</param>
<param desc="parentTemplateID">{9AFF70A1-31E4-48F9-8461-F0DC33F4F77C}</param>
<param desc="hostDatabase ">master</param>
</northwindDataProvider>
Where:
-
Sitecore.Data.DataProviders.NorthwindDataProvider is a type
-
Sitecore.NorthwindDataProvider is the assembly name
- All entries that begin with <param ...> are parameters that you want to pass to the constructor. Here is the corresponding constructor definition:
Sitecore 5.1:
string connection, // Connection string
string table, // Table name
string fieldsNames, // Fields name divided by pipe separator
string idField, // ID Field name (primary key)
string nameField, // Name of field what will be Node’s name
string filter, // "Where" clause
string templateID,// Template name
string parentTemplateID, //Parent template
string hostDatabase // Database into which we integrate
)
Sitecore 5.3:
string connection, // Connection string
string table, // Table name
string fieldsNames, // Fields name divided by pipe separator
string idField, // ID Field name (primary key)
string nameField, // Name field name (name for content editor)
string filter, // "Where" clause
string templateID, // Template name
string parentItemID, // Parent item ID
string hostDatabase // Database into which we integrate
)
2.4. Data Provider at Work
After you install the Northwind Data Provider you will see the Items under /content/home/Northwind Data Provider Item. These are the Items our Data Provider reads from the Northwind database.
Try to change the field values of the Items and save the Item. You will be able to see the newly updated values in the external database.
Sitecore uses caching for Items. To receive fresh data from the database, you need to disable caching for these Items.
Use the Northwind Customer template to create items in Sitecore under /content/home/NorthwindDataProvider. These items will be transferred to MS SQL database. Keep in mind that by default items are created with empty fields, the following setting in web.config will filter out all such items:
<param desc="filter">Country='
To make items appear in Content Editor, either make this setting empty or edit the master to fill the Country field automatically.
3. Download the Sources
Please refer to the Downloads section for the latest version of the Northwind Data Provider.